package id.net.iconpln.bb.server.dao;

import id.net.iconpln.bb.server.util.CommonModule;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import oracle.jdbc.OracleTypes;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

@Repository("PengendalianDao")
public class PengendalianDao {
	@Autowired	private SessionFactory sessionFactory;
	
	public List<Map> GetListPengendalian(String kode, Map map){
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        try {
            String sql = null;
            CallableStatement call;
            if (kode.equals("JADWAL")){
            	sql = "{call bbtrxpengendalian.SETNO_RAKOR(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("no_rakor").toString());
                call.execute();
                
                sql = "{call bbtrxpengendalian.SETID_PLTU(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("id_pltu").toString());
                call.execute();
            }else if (kode.equals("PENGAJUAN_JADWAL")){
            	sql = "{call bbtrxpengendalian.SETNO_PLN_PJBB(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("no_pln_pjbb").toString());
                call.execute();
                
                sql = "{call bbtrxpengendalian.SETID_PLTU(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("id_pltu").toString());
                call.execute();
                
                sql = "{call bbtrxpengendalian.SETID_PEMASOK(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("id_pemasok").toString());
                call.execute();
            }else if (kode.equals("JADWAL_BY_PLTU_PEMASOK")){
                sql = "{call bbtrxpengendalian.SETID_PLTU(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("id_pltu").toString());
                call.execute();
                
                sql = "{call bbtrxpengendalian.SETID_PEMASOK(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("id_pemasok").toString());
                call.execute();
            }else if (kode.equals("RAKOR_BY_PEMBANGKIT")){
            	
            	sql = "{call bbtrxpengendalian.SETID_PLTU(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("id_pltu").toString()); 
                call.execute();
            }else if (kode.equals("RAKOR_BY_PEMASOK")){            	
            	sql = "{call bbtrxpengendalian.SETID_PEMASOK(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("id_pemasok").toString()); 
                call.execute();
            }else if (kode.equals("PEMBANGKIT_BY_RAKOR")){            	
            	sql = "{call bbtrxpengendalian.SETID_PEMASOK(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("id_pemasok").toString()); 
                call.execute();
                
                sql = "{call bbtrxpengendalian.SETNO_RAKOR(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("no_rakor").toString());
                call.execute();
            }
            
            sql = "{? = call bbtrxpengendalian.GETLISTPENGENDALIANBY(?)}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.CURSOR);
            call.setString(2, kode);
            call.execute();
            ResultSet rset = (ResultSet) call.getObject(1);
            lMap = CommonModule.convertResultsetToListLower(rset);
        }catch (Exception e) {
			e.printStackTrace();
		}        
        return lMap;
	}
	
	public List<Map> getNotulenRakor(String kode, Map map){
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        String message = "";
        try {
            String sql = null;
            CallableStatement call;
        	sql = "{call bbtrxpengendalian.SETNO_RAKOR(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("no_rakor").toString());
            call.execute();
            
            sql = "{? = call bbtrxpengendalian.GETLISTPENGENDALIANBY(?)}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.CURSOR);
            call.setString(2, kode);
            call.execute();
            
            ResultSet rset = (ResultSet) call.getObject(1);
            
            while (rset.next()){
	            FileOutputStream fos = new FileOutputStream("report/notulen.pdf");
	            InputStream is  = rset.getBinaryStream(1);
	            byte[] buf = new byte[30000];
	            int read = 0;
	            while ((read = is.read(buf)) > 0) {
	                fos.write(buf, 0, read);
	            }
	            fos.close();
	            is.close();    
	            
	            message = "report/notulen.pdf";
            }
        }catch (Exception e) {
			e.printStackTrace();
			message = e.getMessage();
		}    
        map.put("message", message);
        lMap.add(map);
        return lMap;
	}
	
	public List<Map> getListPengiriman(String kode, Map map){
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        try {
            String sql = null;
            CallableStatement call;
            if (kode.equals("LISTPENGIRIMANBYPEMASOK")){
                sql = "{call bbtrxpengendalian.SETID_PLTU(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("id_pltu").toString());
                call.execute();
            }
            
            System.out.println("kode :" + kode );
            
            sql = "{? = call bbtrxpengendalian.GETLISTPENGENDALIANBY(?)}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.CURSOR);
            call.setString(2, kode);
            call.execute();

            
            ResultSet rset = (ResultSet) call.getObject(1);
            lMap = CommonModule.convertResultsetToListLower(rset);
        }catch (Exception e) {
			e.printStackTrace();
		}        
        return lMap;
	}
	public List<Map> getListPasokan(String kode, Map map){
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        try {
            String sql = null;
            CallableStatement call;

            
            if (kode.equals("LISTPASOKANBYPEMASOK")){
                sql = "{call bbtrxpengendalian.SETID_PLTU(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("id_pltu").toString());
                call.execute();
            }
            
            System.out.println("kode :" + kode );

            sql = "{? = call bbtrxpengendalian.GETLISTPENGENDALIANBY(?)}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.CURSOR);
            call.setString(2, kode);
            call.execute();

            
            ResultSet rset = (ResultSet) call.getObject(1);
            lMap = CommonModule.convertResultsetToListLower(rset);
        }catch (Exception e) {
			e.printStackTrace();
		}        
        return lMap;
	}	
	
	public List<Map> setPeringatanPemutusan(String kode, Map map){
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        try {
            String sql = null;
            CallableStatement call;

            sql = "{? = call bbtrxpengendalian.GETLISTPENGENDALIANBY(?)}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.CURSOR);
            call.setString(2, kode);
            call.execute();

            
            ResultSet rset = (ResultSet) call.getObject(1);
            lMap = CommonModule.convertResultsetToListLower(rset);
        }catch (Exception e) {
			e.printStackTrace();
		}        
        return lMap;
	}		
	
	public List<Map> setBlackList(String kode, Map map){
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        try {
            String sql = null;
            CallableStatement call;

            sql = "{? = call bbtrxpengendalian.GETLISTPENGENDALIANBY(?)}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.CURSOR);
            call.setString(2, kode);
            call.execute();

            
            ResultSet rset = (ResultSet) call.getObject(1);
            lMap = CommonModule.convertResultsetToListLower(rset);
        }catch (Exception e) {
			e.printStackTrace();
		}        
        return lMap;
	}		
	
	
	public List<Map> setBaSt(String kode, Map map){
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        try {
            String sql = null;
            CallableStatement call;

            sql = "{? = call bbtrxpengendalian.GETLISTPENGENDALIANBY(?)}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.CURSOR);
            call.setString(2, kode);
            call.execute();

            
            ResultSet rset = (ResultSet) call.getObject(1);
            lMap = CommonModule.convertResultsetToListLower(rset);
        }catch (Exception e) {
			e.printStackTrace();
		}        
        return lMap;
	}		
	
	public List<Map> insertPengendalianBB(String kode, Map map){
		List<Map> lMap = new ArrayList<Map>();		
        try {
            if (kode.equals("PENGAJUAN_JADWAL")){     
            	lMap = insertPengendalianBB_PENGAJUAN_JADWAL(kode, map);
            	return lMap;
            }else if (kode.equals("JADWAL")){
            	lMap = insertPengendalianBB_APPROVAL_JADWAL(kode, map);
            	return lMap;
            }
        }catch (Exception e) {
			e.printStackTrace();
			map.clear();
            map.put("message", e.getMessage());  
		}
        lMap.add(map);
        return lMap;
	}
	

	private List<Map> insertPengendalianBB_PENGAJUAN_JADWAL(String kode, Map map){
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();        
        try{
        	String sql = null;
            CallableStatement call = null;
            
            sql = "{call bbtrxpengendalian.SETTGL_TA(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("tgl_ta").toString());
            call.execute();
            
            sql = "{call bbtrxpengendalian.SETTGL_TD(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("tgl_td").toString());
            call.execute();
            
            sql = "{call bbtrxpengendalian.SETID_PEMASOK(?)}";
            call = con.prepareCall(sql);
            call.setBigDecimal(1, new BigDecimal(map.get("id_pemasok").toString()));
            call.execute();
            
            sql = "{call bbtrxpengendalian.SETID_TRANSPORTIR(?)}";
            call = con.prepareCall(sql);
            call.setBigDecimal(1, new BigDecimal(map.get("id_transportir").toString()));
            call.execute();
            
            sql = "{call bbtrxpengendalian.SETID_PLTU(?)}";
            call = con.prepareCall(sql);
            call.setBigDecimal(1, new BigDecimal(map.get("id_pltu").toString()));
            call.execute();
            
            sql = "{call bbtrxpengendalian.SETTANGGAL(?)}";
            call = con.prepareCall(sql);
            DateFormat df = new SimpleDateFormat("dd/mm/yyyy");
            Date tgl = df.parse(map.get("tanggal").toString());
            call.setDate(1, new java.sql.Date(tgl.getTime()));
            call.execute();
            
            sql = "{call bbtrxpengendalian.SETVOLUME(?)}";
            call = con.prepareCall(sql);
            call.setBigDecimal(1, new BigDecimal(map.get("volume").toString()));
            call.execute();
            
            sql = "{call bbtrxpengendalian.SETCREATEBY(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("user").toString());
            call.execute();
            
            sql = "{call bbtrxpengendalian.SETAPP_ACCESS(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("app_access").toString());
            call.execute();
            
            sql = "{call bbtrxpengendalian.SETNO_PLN_PJBB(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("no_pln_pjbb").toString());
            call.execute();
            
            sql = "{call bbtrxpengendalian.SETNO_JADWAL(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("no_jadwal").toString());
            call.execute();
            
            sql = "{? = call bbtrxpengendalian.INSERTBBPENGENDALIAN(?,?)}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.VARCHAR);
            call.setString(2, kode);
            call.setString(3, map.get("crud").toString());            
            call.execute();
            
            map.put("message", call.getString(1));    
            System.out.println(call.getString(1));
        }catch (Exception e) {
        	map.put("message", e.getMessage());    
        	e.printStackTrace();
		}
        lMap.add(map);
        return lMap;
	}
	
	private List<Map> insertPengendalianBB_APPROVAL_JADWAL(String kode, Map map){
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();        
        try{
        	String sql = null;
            CallableStatement call = null;
                        
            sql = "{call bbtrxpengendalian.SETID_PLTU(?)}";
            call = con.prepareCall(sql);
            call.setBigDecimal(1, new BigDecimal(map.get("id_pltu").toString()));
            call.execute();
                                   
            sql = "{call bbtrxpengendalian.SETCREATEBY(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("user").toString());
            call.execute();
            
            sql = "{call bbtrxpengendalian.SETAPP_ACCESS(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("app_access").toString());
            call.execute();
            
            sql = "{call bbtrxpengendalian.SETNO_JADWAL(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("no_jadwal").toString());
            call.execute();
            
            sql = "{call bbtrxpengendalian.SETNO_RAKOR(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("no_rakor").toString());
            call.execute();
            
            sql = "{call bbtrxpengendalian.SETID_RAKOR(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("id_rakor").toString());
            call.execute();
            
            sql = "{? = call bbtrxpengendalian.INSERTBBPENGENDALIAN(?,?)}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.VARCHAR);
            call.setString(2, kode);
            call.setString(3, map.get("crud").toString());            
            call.execute();
            
            map.put("message", call.getString(1));    
            System.out.println(call.getString(1));
        }catch (Exception e) {
        	map.put("message", e.getMessage());    
        	e.printStackTrace();
		}
        lMap.add(map);
        return lMap;
	}
	
	@Transactional(readOnly=false)
	public List<Map> insertNotulenRakor(String kode, Map map, File file){
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        try {
            String sql = null;
            CallableStatement call;
            	sql = "{call bbtrxpengendalian.SETNO_RAKOR(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("no_rakor").toString());
                call.execute();
                
                sql = "{call bbtrxpengendalian.SETJUDUL(?)}";
                call = con.prepareCall(sql);
                call.setString(1, file.getName());
                call.execute();
                
                sql = "{call bbtrxpengendalian.SETCREATEBY(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("user").toString());
                call.execute();
                
                sql = "{call bbtrxpengendalian.SETAPP_ACCESS(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("app_access").toString());
                call.execute();
                
                sql = "{call bbtrxpengendalian.SETFILE_NOTULEN(?)}";
                call = con.prepareCall(sql);
                byte[] buffer = new byte[(int)file.length()];
                FileInputStream fis = new FileInputStream(file);
                call.setBinaryStream(1, fis, (int) file.length());
                call.execute();
                
                file.delete();
                
                sql = "{? = call bbtrxpengendalian.INSERTBBPENGENDALIAN(?,?)}";
                call = con.prepareCall(sql);
                call.registerOutParameter(1, OracleTypes.VARCHAR);
                call.setString(2, kode);
                call.setString(3, map.get("crud").toString());            
                call.execute();
                
                map.put("message", call.getString(1));    
                System.out.println(call.getString(1));
            }catch (Exception e) {
            	map.put("message", e.getMessage());    
            	e.printStackTrace();
    		}
            lMap.add(map);
        return lMap;
	}
	
}
